clear
set more off

cap log close
log using "$logdir/get_data/nrp/get_nrp_w2.log", replace

/* put into tinlist */
use $nrpdir/nrp_raw, clear
keep prim_tin
export delimited using $nrpdir/prim_tinlist.csv, replace




set odbcmgr unixodbc

clear

odbc list
#delimit ;

odbc load, exec("

	SET TEMPORARY OPTION escape_character = on; 
		CREATE TABLE #tinlist 
		 (prim_tin bigint)
	   ;
		LOAD TABLE #tinlist 
			(prim_tin '\n') 
			USING CLIENT FILE '$nrpdir/prim_tinlist.csv'
			SKIP 1 ESCAPES OFF
		;

			/* primary */
			select
				a.prim_tin,
				b.REDACTED as payer_id,
				b.REDACTED as wgs,
				1 as prim,
				b.REDACTED as formdate
			into #prim
			from #tinlist as a
				inner join REDACTED as b
					on(a.REDACTED = b.REDACTED)
			where b.REDACTED = 2014
				and b.REDACTED > 0
				and b.REDACTED <= 4;
			
			/* secondary */
			select
				a.prim_tin,
				c.REDACTED as payer_id,
				c.REDACTED as wgs,
				0 as prim,
				c.REDACTED as formdate
			into #sec
			from #tinlist as a
				inner join REDACTED as b
					on(a.REDACTED = b.REDACTED)
				inner join REDACTED as c
					on(b.REDACTED = c.REDACTED and b.REDACTED = c.REDACTED)
			where b.REDACTED = 2014
				and c.REDACTED > 0
				and c.REDACTED <= 4
				and b.REDACTED = 2
				and b.REDACTED > 0;
				
				
			/* take union */
			select
				convert(bigint,prim_tin) as prim_tin,
				convert(bigint,payer_id) as payer_id,
				wgs,
				prim,
				formdate
			from #prim
			union
			select
				convert(bigint,prim_tin) as prim_tin,
				convert(bigint,payer_id) as payer_id,
				wgs,
				prim,
				formdate
			from #sec;
			
") multistatement
	dsn("prod_cdw") user($usr) password($pwd) sqlshow;


#delimit cr
di _N
tab prim
cap destring prim_tin payer_id, replace force

sort prim_tin payer_id prim formdate
by prim_tin payer_id prim: keep if _n == _N

collapse (sum) wgs_w2 = wgs, by(prim_tin) fast
qui save $nrpdir/w2, replace

cap log close

